package com.sinopac.model.DataBatch;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import netfusion.jbranch.database.DataAccessManager;

import org.apache.commons.lang.StringUtils;

import com.opensymphony.xwork2.util.logging.Logger;
import com.opensymphony.xwork2.util.logging.LoggerFactory;
import com.sinopac.util.XmlUtil;

public class DataBatch {

	private String userDBname = null;
	private Logger logger = LoggerFactory.getLogger(this.getClass());

	public void execute_db() throws SQLException {
		String start_end = "";
		DataAccessManager dam = new DataAccessManager();
		XmlUtil xml = new XmlUtil();
		xml.otpServer();
		userDBname = xml.map.get("userDBname");

		//記錄資料抄寫程式開始執行在Logs裡
		start_end = "s";
		copyDataStart_or_End(start_end);
		
		//將資料從userDB撈出並暫存至userDB_L的List裡面。
		List<Map<String, Object>> userDB_L = new ArrayList();
		StringBuffer query1 = new StringBuffer("select * from " + userDBname
				+ ".dbo.db_cif_dds");
		dam.setQueryString(query1.toString());
		userDB_L = dam.exeQuery();

		//檢查user的otp_status根本機的token_status是否相同
		checkData(userDB_L);
		
		//記錄資料抄寫程式結束執行在Logs裡
		start_end = "e";
		copyDataStart_or_End(start_end);

		start_end = "";
	}

	// 記錄資料抄寫程式開始或結束進Logs
	private void copyDataStart_or_End(String start_end) throws SQLException {
		if (start_end == "s") {
			logger.info("===copyData Program_Start===");
		}

		DataAccessManager dam = new DataAccessManager();
		StringBuffer insert_sql = new StringBuffer(
				"Insert into Logs (log_time, job_id, job_code, operator, manager ) ");
		insert_sql.append(" values(getDate(), :random, :job_code, '', '' )");
		String random = produceRandom();

		dam.setObject("random", random);
		if (start_end == "s") {
			// 資料抄寫開始
			dam.setObject("job_code", "6000");
		}else{
			// 資料抄寫結束
			dam.setObject("job_code", "6001");
		}

		dam.setQueryString(insert_sql.toString());
		dam.exeUpdate();

		if (start_end != "s") {
			logger.info("===copyData Program_End===");
		}
	}

	// 檢查userDB資料是否與local資料一致,並做更新、新增、記錄異常狀態log
	private void checkData(List<Map<String, Object>> userDB_L)
			throws SQLException {
		logger.info("===checkData_Start===");

		DataAccessManager dam = new DataAccessManager();

		for (Map<String, Object> map1 : userDB_L) {
			// userDB撈出的客戶id前18碼
			String cust_id01 = StringUtils.trimToEmpty(map1
					.get("db_cif_cifkey").toString());
			// userDB撈出的客戶id中間1碼
			String cust_id02 = StringUtils.trimToEmpty(map1
					.get("db_cif_ciferr").toString());
			// userDB撈出的客戶id後2碼
			String cust_id03 = StringUtils.trimToEmpty(map1
					.get("db_cif_cifnat").toString());
			// 將userDB撈出的客戶id組起來
			String cust_id_whole = cust_id01 + "-" + cust_id02 + "-"
					+ cust_id03;

			// 用user的db傳來的客戶ID做select
			StringBuffer query1 = new StringBuffer(
					"select * from Customers where customer_id =:customer_id");
			dam.setObject("customer_id", cust_id_whole);
			dam.setQueryString(query1.toString());
			List<Map<String, Object>> localDB_L = dam.exeQuery();
			
			//判斷userDB來的客戶ID是否在local的DB裡
			if (localDB_L.isEmpty()) {
				//如果不在localDB,將userDB來的資料,新增進Customers
				insertData(userDB_L);
			}else{
				//userDB來的客戶ID存在localDB裡
				for (Map<String, Object> map2 : localDB_L) {
					// 判斷如果此客戶為集團戶
					if (StringUtils.trimToEmpty(map2.get("is_group").toString()) == "1") {
						dam = new DataAccessManager();
						StringBuffer query2 = new StringBuffer(
								"select * from Tokens where token_esn = ");
						query2.append("(select token_esn from Token_Customer where related_to =:groupId ) ");
						dam.setObject("groupId", StringUtils.trimToEmpty(map2.get("group_id").toString()));
					}else {
					// 判斷如果此客戶為個人戶
						StringBuffer query2 = new StringBuffer(
								"select * from Tokens where token_esn = ");
						query2.append("(select token_esn from Token_Customer where related_to =:customer_id ) ");
						dam.setObject("customer_id", cust_id_whole);
					}

					// 將query資料暫存進list裡
					List<Map<String, Object>> temp_L = dam.exeQuery();
					// 判斷userDB的客戶的otp_status是否等於localDB的客戶的token_status
					for (Map<String, Object> map3 : temp_L) {
						String userDB_otp_Status = StringUtils.trimToEmpty(map1.get("db_cif_otp").toString());// 客戶的OTP_status
						String localDB_token_status = StringUtils.trimToEmpty(map3.get("token_status")
										.toString());// 本機的token_stauts
						// 如果userDB的客戶的otp_status不等於localDB的客戶的token_status，就記異常Log
						if (userDB_otp_Status != localDB_token_status) {
							// 紀錄異常log
							abnormal_log_insert(cust_id_whole, userDB_otp_Status, localDB_token_status);
						}
						//不管資料相不相同，都update客戶資料
						updateData(userDB_L);
					}
				}
			}
		}
		logger.info("===checkData_End===");
	}

	//從userDB來的資料	新增資料進Customers
	private void insertData(List<Map<String, Object>> userDB_L) throws SQLException {
		DataAccessManager dam = new DataAccessManager();
		for (Map<String, Object> map1 : userDB_L) {
			// userDB撈出的客戶id前18碼
			String cust_id01 = StringUtils.trimToEmpty(map1.get("db_cif_cifkey").toString());
			// userDB撈出的客戶id中間1碼
			String cust_id02 = StringUtils.trimToEmpty(map1.get("db_cif_ciferr").toString());
			// userDB撈出的客戶id後2碼
			String cust_id03 = StringUtils.trimToEmpty(map1.get("db_cif_cifnat").toString());
			// 將userDB撈出的客戶id組起來
			String cust_id_whole = cust_id01 + "-" + cust_id02 + "-" + cust_id03;
			
			StringBuffer query2 = new StringBuffer("Insert into Customers ");
			//2012-11-23 新增is_group, is_group_main 塞入，預設值為0
			query2.append("( customer_id, customer_name, AO_code, customer_contact, customer_phone, customer_address, otp_status, is_group, is_group_main,");
			query2.append(" db_cif_faxtype1, db_cif_faxtype2, db_cif_faxtype3, db_cif_faxtype4, db_cif_faxtype5, db_cif_faxtype6) ");
			query2.append(" values(:customer_id, :customer_name, :AO_code, :customer_contact, :customer_phone, :customer_address, :otp_status, :is_group, :is_group_main, ");
			query2.append(" :db_cif_faxtype1, :db_cif_faxtype2, :db_cif_faxtype3, :db_cif_faxtype4, :db_cif_faxtype5, :db_cif_faxtype6)");
	
			dam.setObject("customer_id", cust_id_whole);
			dam.setObject("customer_name", StringUtils.trimToEmpty(map1.get("db_cif_name").toString()));
			dam.setObject("AO_code", StringUtils.trimToEmpty(map1.get("db_cif_careno").toString()));
			dam.setObject("customer_contact", StringUtils.trimToEmpty(map1.get("db_cif_pname").toString()));
			dam.setObject("customer_phone", StringUtils.trimToEmpty(map1.get("db_cif_telno1").toString()));
			dam.setObject("customer_address", StringUtils.trimToEmpty(map1.get("db_cif_engadr").toString()));
			dam.setObject("otp_status", StringUtils.trimToEmpty(map1.get("db_cif_otp").toString()));
			//2012-11-23 新增，將是否為集團戶預設為0 否
			dam.setObject("is_group", "0");
			//2012-11-23 新增，將是否為集團戶主帳戶預設為0 否
			dam.setObject("is_group_main", "0");
			dam.setObject("db_cif_faxtype1", StringUtils.trimToEmpty(map1.get("db_cif_faxtype1").toString()));
			dam.setObject("db_cif_faxtype2", StringUtils.trimToEmpty(map1.get("db_cif_faxtype2").toString()));
			dam.setObject("db_cif_faxtype3", StringUtils.trimToEmpty(map1.get("db_cif_faxtype3").toString()));
			dam.setObject("db_cif_faxtype4", StringUtils.trimToEmpty(map1.get("db_cif_faxtype4").toString()));
			dam.setObject("db_cif_faxtype5", StringUtils.trimToEmpty(map1.get("db_cif_faxtype5").toString()));
			dam.setObject("db_cif_faxtype6", StringUtils.trimToEmpty(map1.get("db_cif_faxtype6").toString()));
	
			dam.setQueryString(query2.toString());
			dam.exeUpdate();
		}
	}

	//更新客戶資料
	private void updateData(List<Map<String, Object>> userDB_L) throws SQLException {
		DataAccessManager dam = new DataAccessManager();
		
		for (Map<String, Object> map1 : userDB_L) {
			StringBuffer query2 = new StringBuffer(
					"update Customers set customer_name =:customer_name, AO_code=:AO_code, customer_contact =:customer_contact, customer_phone =:customer_phone, customer_address =:customer_address, otp_status =:otp_status, ");
			query2.append(" db_cif_faxtype1 =:db_cif_faxtype1, db_cif_faxtype2 =:db_cif_faxtype2, db_cif_faxtype3 =:db_cif_faxtype3, db_cif_faxtype4 =:db_cif_faxtype4, db_cif_faxtype5 =:db_cif_faxtype5, db_cif_faxtype6 =:db_cif_faxtype6");
			dam.setObject("customer_name", StringUtils.trimToEmpty(map1
					.get("db_cif_name").toString()));
			dam.setObject("AO_code", StringUtils.trimToEmpty(map1.get(
					"db_cif_careno").toString()));
			dam.setObject("customer_contact", StringUtils.trimToEmpty(map1
					.get("db_cif_pname").toString()));
			dam.setObject("customer_phone", StringUtils.trimToEmpty(map1
					.get("db_cif_telno1").toString()));
			dam.setObject("customer_address", StringUtils.trimToEmpty(map1
					.get("db_cif_engadr").toString()));
			dam.setObject("otp_status", StringUtils.trimToEmpty(map1.get(
					"db_cif_otp").toString()));
			dam.setObject("db_cif_faxtype1", StringUtils.trimToEmpty(map1
					.get("db_cif_faxtype1").toString()));
			dam.setObject("db_cif_faxtype2", StringUtils.trimToEmpty(map1
					.get("db_cif_faxtype2").toString()));
			dam.setObject("db_cif_faxtype3", StringUtils.trimToEmpty(map1
					.get("db_cif_faxtype3").toString()));
			dam.setObject("db_cif_faxtype4", StringUtils.trimToEmpty(map1
					.get("db_cif_faxtype4").toString()));
			dam.setObject("db_cif_faxtype5", StringUtils.trimToEmpty(map1
					.get("db_cif_faxtype5").toString()));
			dam.setObject("db_cif_faxtype6", StringUtils.trimToEmpty(map1
					.get("db_cif_faxtype6").toString()));
	
			dam.setQueryString(query2.toString());
			dam.exeUpdate();
		}
	}
	
	//記錄異常狀態的log
	private void abnormal_log_insert(String customer_id,
			String userDB_otp_Status, String localDB_token_status)
			throws SQLException {
		logger.info("===abnormal_log record Start===");
		DataAccessManager dam = new DataAccessManager();
		StringBuffer sql_insert = new StringBuffer(
				"Insert into Logs (log_time, job_id, job_code, customer_id, unisys_status, token_status, operator, manager ) ");
		sql_insert
				.append(" values(getDate(), :random,'6002', :customer_id, :userDB_otp_Status, :localDB_token_status, '', '' )");

		// 產生隨機作業序號job_id
		String random_ser = produceRandom();
		System.out.println("random_ser:" + random_ser);

		dam.setObject("random", random_ser);
		dam.setObject("customer_id", customer_id);
		dam.setObject("userDB_otp_Status", userDB_otp_Status);
		dam.setObject("localDB_token_status", localDB_token_status);
		dam.setQueryString(sql_insert.toString());
		dam.exeUpdate();

		logger.info("===abnormal_log record End===");
	}

	//產生亂數給job_id當序號使用　日期時分秒毫秒+兩位亂數
	private String produceRandom() {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmssSSS");
		StringBuffer sb = new StringBuffer(sdf.format(new Date()));

		int num = (int) (Math.random() * 99 + 1);// 先產生4位數字的亂數,不然長度不夠裝

		String rd = sb.deleteCharAt(14).toString() + String.valueOf(num);

		return rd;
	}
	
}

